package com.yx.outSourcing.utils;

import com.yx.outSourcing.annotation.ExcelColumn;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.lang.reflect.Field;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

public class ExcelUtil {

    // 导入 Excel 数据到对象列表
    public static <T> List<T> importExcel(MultipartFile file, Class<T> clazz) throws Exception {
        List<T> list = new ArrayList<>();
        try (InputStream inputStream = file.getInputStream()) {
            Workbook workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表

            // 读取 Excel 表头（第一行）
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) {
                throw new RuntimeException("Excel文件没有表头");
            }
            int columnCount = headerRow.getPhysicalNumberOfCells();

            // 获取类的所有字段，并根据ExcelColumn注解排序
            Map<String, Field> fieldMap = getFieldMap(clazz);

            // 使用反射读取每一行数据并映射到对象
            for (int i = 1; i <= sheet.getLastRowNum(); i++) { // 从第二行开始
                Row row = sheet.getRow(i);
                if (row == null) continue;

                T obj = clazz.getDeclaredConstructor().newInstance();

                for (int j = 0; j < columnCount; j++) {
                    Cell cell = row.getCell(j);
                    if (cell == null) continue;

                    Cell headerCell = headerRow.getCell(j);
                    if (headerCell == null) continue;

                    String columnName = headerCell.getStringCellValue();
                    Field field = fieldMap.get(columnName);
                    if (field != null) {
                        field.setAccessible(true);
                        field.set(obj, getCellValue(cell));
                    }
                }
                list.add(obj);
            }
        }
        return list;
    }

    // 获取字段映射表（列名 -> 字段）
    private static <T> Map<String, Field> getFieldMap(Class<T> clazz) {
        Map<String, Field> fieldMap = new HashMap<>();
        Field[] fields = clazz.getDeclaredFields();

        for (Field field : fields) {
            ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
            if (annotation != null) {
                String columnName = annotation.name().isEmpty() ? field.getName() : annotation.name();
                fieldMap.put(columnName, field);
            } else {
                fieldMap.put(field.getName(), field);
            }
        }

        return fieldMap;
    }

    // 导出数据到 Excel
    public static <T> void exportExcel(List<T> data, Class<T> clazz, OutputStream outputStream) throws Exception {
        if (data == null || data.isEmpty()) {
            throw new RuntimeException("导出数据不能为空");
        }

        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Sheet1");
            CreationHelper creationHelper = workbook.getCreationHelper();

            // 获取并排序字段
            List<Field> sortedFields = getSortedFields(clazz);

            // 创建表头
            Row headerRow = sheet.createRow(0);
            for (int i = 0; i < sortedFields.size(); i++) {
                Field field = sortedFields.get(i);
                ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                String columnName = annotation != null && !annotation.name().isEmpty() ? annotation.name() : field.getName();
                headerRow.createCell(i).setCellValue(columnName);
            }

            // 创建单元格样式
            Map<String, CellStyle> cellStyles = createCellStyles(workbook);

            // 填充数据
            for (int i = 0; i < data.size(); i++) {
                Row row = sheet.createRow(i + 1);
                T obj = data.get(i);

                for (int j = 0; j < sortedFields.size(); j++) {
                    Field field = sortedFields.get(j);
                    field.setAccessible(true);
                    Object value = field.get(obj);

                    Cell cell = row.createCell(j);
                    ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);

                    if (value != null) {
                        if (value instanceof LocalDateTime) {
                            String format = annotation != null ? annotation.dateFormat() : "yyyy-MM-dd HH:mm:ss";
                            DateTimeFormatter formatter = DateTimeFormatter.ofPattern(format);
                            cell.setCellValue(((LocalDateTime) value).format(formatter));
                            cell.setCellStyle(cellStyles.get("date"));
                        } else if (value instanceof Number) {
                            cell.setCellValue(((Number) value).doubleValue());
                            cell.setCellStyle(cellStyles.get("number"));
                        } else if (value instanceof Boolean) {
                            cell.setCellValue((Boolean) value);
                            cell.setCellStyle(cellStyles.get("boolean"));
                        } else {
                            cell.setCellValue(value.toString());
                            cell.setCellStyle(cellStyles.get("string"));
                        }
                    } else {
                        cell.setCellValue("");
                    }
                }
            }

            // 自动调整列宽
            for (int i = 0; i < sortedFields.size(); i++) {
                sheet.autoSizeColumn(i);
            }

            workbook.write(outputStream);
            outputStream.flush();
        }
    }

    // 获取排序后的字段列表
    private static <T> List<Field> getSortedFields(Class<T> clazz) {
        List<Field> fields = new ArrayList<>(Arrays.asList(clazz.getDeclaredFields()));

        fields.sort((f1, f2) -> {
            ExcelColumn a1 = f1.getAnnotation(ExcelColumn.class);
            ExcelColumn a2 = f2.getAnnotation(ExcelColumn.class);

            int order1 = a1 != null ? a1.order() : Integer.MAX_VALUE;
            int order2 = a2 != null ? a2.order() : Integer.MAX_VALUE;

            return Integer.compare(order1, order2);
        });

        return fields;
    }

    // 创建单元格样式
    private static Map<String, CellStyle> createCellStyles(Workbook workbook) {
        Map<String, CellStyle> cellStyles = new HashMap<>();

        // 字符串样式
        CellStyle stringStyle = workbook.createCellStyle();
        DataFormat dataFormat = workbook.createDataFormat();
        stringStyle.setDataFormat(dataFormat.getFormat("@"));
        cellStyles.put("string", stringStyle);

        // 数字样式
        CellStyle numberStyle = workbook.createCellStyle();
        numberStyle.setDataFormat(dataFormat.getFormat("0.00"));
        cellStyles.put("number", numberStyle);

        // 日期样式
        CellStyle dateStyle = workbook.createCellStyle();
        dateStyle.setDataFormat(dataFormat.getFormat("yyyy-MM-dd HH:mm:ss"));
        cellStyles.put("date", dateStyle);

        // 布尔样式
        CellStyle booleanStyle = workbook.createCellStyle();
//        booleanStyle.setDataFormat(dataFormat.getFormat("BOOLEAN"));
        cellStyles.put("boolean", booleanStyle);

        return cellStyles;
    }

    // 获取单元格值
    private static Object getCellValue(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                } else {
                    return cell.getNumericCellValue();
                }
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                return cell.getCellFormula();
            default:
                return null;
        }
    }
}
